﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for CatalogAccess
/// </summary>
public class XeploaiClass //thu thoi ma
{
    public int ID { get; set; }
    public string XepLoai { get; set; }
}
public class DoTuoiClass
{
    public int DoTuoi20 { get; set; }
    public int DoTuoi25 { get; set; }
    public int DoTuoi30 { get; set; }
    public int DoTuoi35 { get; set; }
    public int DoTuoi40 { get; set; }
    public int DoTuoi42 { get; set; }
}
public class TieuChiRenLuyen
{
    public int STT { get; set; }
    public string NoiDung { get; set; }
    public string Diem { get; set; }
    public int XepLoaiID { get; set; }
    public string DanhGiaCuaDoanVien { get; set; }
    public string DanhGiaCuaCoSoDoan { get; set; }
    public string GhiChu { get; set; }
}
public class KinhTeGiaDinhClass
{
    public int ID { get; set; }
    public string ThangNam { get; set; }
    public string NganhBac { get; set; }
    public string HeSo { get; set; }
}
public class CatalogAccess
{
    #region "Khen thuong ky luat"
    public static bool InsertKhenThuongKyLuat(string ngay, int danhhieu,string capkhenthuongkyluat, string noidung, string lydo, string madoanvien,bool khenthuong)
    {
        _dbDataContext _db = new _dbDataContext();
        tblKhenThuongKyLuat _table = new tblKhenThuongKyLuat();
        _table.Ngay = ConvertToDate(ngay);
        if(danhhieu!=0)
        {
            _table.DanhHieuID = danhhieu;
        }
        _table.CapKhenThuong_KyLuat = capkhenthuongkyluat;
        _table.NoiDung = noidung;
        _table.LyDo = lydo;
        _table.MaDoanVien = madoanvien;
        _table.KhenThuong = khenthuong;
        try
        {
            _db.tblKhenThuongKyLuats.InsertOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateKhenThuongKyLuat(int id,string ngay, int danhhieu,string capkhenthuongkyluat, string noidung, string lydo)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblKhenThuongKyLuats.Single(r=>r.ID==id);
        _table.Ngay = ConvertToDate(ngay);
        if (danhhieu != 0)
        {
            _table.DanhHieuID = danhhieu;
        }
        _table.CapKhenThuong_KyLuat = capkhenthuongkyluat;
        _table.NoiDung = noidung;
        _table.LyDo = lydo;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteKhenThuongKyLuat(int id)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblKhenThuongKyLuats.Single(r => r.ID == id);
        try
        {
            _db.tblKhenThuongKyLuats.DeleteOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    #endregion
    #region "Danh gia doan vien"
    
    public static bool InsertDanhGiaDoanVien(int name, int xeploai, string ghichu, string nhanxet, string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblDanhGiaGoanVien _table = new tblDanhGiaGoanVien();
        _table.NamDanhGia = name;
        _table.XepLoai = xeploai;
        _table.GhiChu = ghichu;
        _table.NhanXet = nhanxet;
        _table.MaDoanVien = madoanvien;
        try
        {
            _db.tblDanhGiaGoanViens.InsertOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateDanhGiaDoanVien(int id,int nam, int xeploai, string ghichu, string nhanxet)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblDanhGiaGoanViens.Single(r=>r.ID==id);
        _table.NamDanhGia = nam;
        _table.XepLoai = xeploai;
        _table.GhiChu = ghichu;
        _table.NhanXet = nhanxet;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }

    #endregion
    #region "Quản lý danh mục kỹ năng"
    public static DoTuoiClass GetDoTuoi()
    {
        DoTuoiClass list = new DoTuoiClass();
        list.DoTuoi20 = 20;
        list.DoTuoi25 = 25;
        list.DoTuoi30 = 30;
        list.DoTuoi35 = 35;
        list.DoTuoi40 = 40;
        list.DoTuoi42 = 42;
        return list;
    }
    public static List<XeploaiClass> GetXepLoai()
    {
        List<XeploaiClass> list = new List<XeploaiClass>()
        {
            new XeploaiClass(){ID=1,XepLoai="---Chọn---"},
            new XeploaiClass(){ID=2,XepLoai="Xuất sắc"},
            new XeploaiClass(){ID=3,XepLoai="Giỏi"},
            new XeploaiClass(){ID=4,XepLoai="Khá"},
            new XeploaiClass(){ID=5,XepLoai="Trung bình"},
            new XeploaiClass(){ID=6,XepLoai="Không hoàn thành"},
            new XeploaiClass(){ID=7,XepLoai="Yếu"},
            new XeploaiClass(){ID=8,XepLoai="Kém"},
            new XeploaiClass(){ID=9,XepLoai="Không phân loại"},
        };
        return list;
    }

    public static List<XeploaiClass> GetXepLoaiDanhGiaDoanVien()
    {
        List<XeploaiClass> list = new List<XeploaiClass>()
        {
            new XeploaiClass(){ID=1,XepLoai="---Chọn---"},
            new XeploaiClass(){ID=2,XepLoai="Xuất sắc"},
            new XeploaiClass(){ID=3,XepLoai="Khá"},
            new XeploaiClass(){ID=4,XepLoai="Trung bình"},
            new XeploaiClass(){ID=5,XepLoai="Yếu"},
        };
        return list;
    }


    public static List<XeploaiClass> GetXepLoaiDanhGiaCoSoDoan()
    {
        List<XeploaiClass> list = new List<XeploaiClass>()
        {
            new XeploaiClass(){ID=1,XepLoai="---Chọn---"},
            new XeploaiClass(){ID=2,XepLoai="Xuất sắc"},
            new XeploaiClass(){ID=3,XepLoai="Vững mạnh"},
            new XeploaiClass(){ID=4,XepLoai="Khá"},
            new XeploaiClass(){ID=5,XepLoai="Trung bình"},
            new XeploaiClass(){ID=6,XepLoai="Yếu"},
        };
        return list;
    }

    public static List<XeploaiClass> GetXepLoaiRenLuyenDoanVien()
    {
        List<XeploaiClass> list = new List<XeploaiClass>()
        {
            new XeploaiClass(){ID=1,XepLoai="---Chọn---"},
            new XeploaiClass(){ID=2,XepLoai="Xuất sắc"},
            new XeploaiClass(){ID=4,XepLoai="Khá"},
            new XeploaiClass(){ID=5,XepLoai="Trung bình"},
            new XeploaiClass(){ID=6,XepLoai="Không hoàn thành"},
        };
        return list;
    }

    public static void InsertKyNangNgoaiNgu(int ngoainguid,string madoanvien) 
    {
        _dbDataContext _db = new _dbDataContext();
        tblKyNangNgoaiNgu _table = new tblKyNangNgoaiNgu();
        _table.NgoaiNguID = ngoainguid;
        _table.Nghe = 1;
        _table.Noi = 1;
        _table.Doc = 1;
        _table.Viet = 1;
        _table.MaDoanVien = madoanvien;
        _table.Chon = false;
        try
        {
            _db.tblKyNangNgoaiNgus.InsertOnSubmit(_table);
            _db.SubmitChanges();
        }
        catch { }
    }
    public static void UpdateKyNangNgoaiNgu(int id,int nghe,int noi,int doc,int viet,bool chon)
    {
        _dbDataContext _db = new _dbDataContext();
        var kynang = _db.tblKyNangNgoaiNgus.Single(r=>r.ID==id);
        if (chon == true)
        {
            kynang.Nghe = nghe;
            kynang.Noi = noi;
            kynang.Doc = doc;
            kynang.Viet = viet;
            kynang.Chon = chon;
        }
        else
        {
            kynang.Nghe = 1;
            kynang.Noi = 1;
            kynang.Doc = 1;
            kynang.Viet = 1;
            kynang.Chon = chon;
        }
        try
        {
            _db.SubmitChanges();
        }
        catch { }
    }
    public static void InsertKyNangTinHoc(int tinhocid, string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblKyNangTinHoc _table = new tblKyNangTinHoc();
        _table.TinHocID = tinhocid;
        _table.KyNang = 1;
        _table.MaDoanVien = madoanvien;
        _table.Chon = false;
        try
        {
            _db.tblKyNangTinHocs.InsertOnSubmit(_table);
            _db.SubmitChanges();
        }
        catch { }
    }
    public static void UpdateKyNangTinHoc(int id, int kynang, bool chon)
    {
           _dbDataContext _db = new _dbDataContext();
        var val = _db.tblKyNangTinHocs.Single(r=>r.ID==id);
        if (chon == true)
        {
            val.KyNang = kynang;
            val.Chon = chon;
        }
        else
        {
            val.KyNang = 1;
            val.Chon=chon;
        }
        try
        {
            _db.SubmitChanges();
        }
        catch { }
    }
    public static void InsertKyNangKhac(int kynangkhacid, string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblKyNangKhac _table = new tblKyNangKhac();
        _table.KyNangKhacID = kynangkhacid;
        _table.KyNang = 1;
        _table.MaDoanVien = madoanvien;
        _table.Chon = false;
        try
        {
            _db.tblKyNangKhacs.InsertOnSubmit(_table);
            _db.SubmitChanges();
        }
        catch { }
    }
    public static void UpdateKyNangKhac(int id, int kynang, bool chon)
    {
        _dbDataContext _db = new _dbDataContext();
        var val = _db.tblKyNangKhacs.Single(r => r.ID == id);
        if (chon == true)
        {
            val.KyNang = kynang;
            val.Chon = chon;
        }
        else
        {
            val.KyNang = 1;
            val.Chon = chon;
        }
        try
        {
            _db.SubmitChanges();
        }
        catch { }
    }
    #endregion
    #region "Quản lý các danh mục"
    public static bool InsertDanhMuc(string tendanhmuc,int loai)
    {
        _dbDataContext _db = new _dbDataContext();
        tblDanhMuc _table=new tblDanhMuc();
        _table.TenDanhMuc=tendanhmuc;
        _table.TDMKhongDau = SecurityLib.ToolsMore.ConvertToUnSignMutile(tendanhmuc).ToUpper().Replace("-"," ");
        _table.Loai = loai;
        try{
            _db.tblDanhMucs.InsertOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }catch{return false;}
    }
    public static bool UpdateDanhMuc(int id, string tendanhmuc)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblDanhMucs.Single(r=>r.ID==id);
        _table.TenDanhMuc = tendanhmuc;
        _table.TDMKhongDau = SecurityLib.ToolsMore.ConvertToUnSignMutile(tendanhmuc).ToUpper().Replace("-", " ");
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteDanhMuc(int id)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblDanhMucs.Single(r=>r.ID==id);
        try
        {
            _db.tblDanhMucs.DeleteOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    #endregion
    #region "Quản lý phường xã"
    public static bool InsertPhuong(string _tenphuong, int _quanid)
    {
        _dbDataContext _db = new _dbDataContext();
        tblPhuong tblphuong = new tblPhuong();
        tblphuong.TenPhuong = _tenphuong;
        tblphuong.QuanID = _quanid;
        try
        {
            _db.tblPhuongs.InsertOnSubmit(tblphuong);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdatePhuong(int id, string _tenphuong)
    {
        _dbDataContext _db = new _dbDataContext();
        var val = _db.tblPhuongs.Single(r=>r.ID==id);
        val.TenPhuong = _tenphuong;
        try { _db.SubmitChanges(); return true; }
        catch { return false; }
    }
    public static bool DeletePhuong(int id)
    {
        _dbDataContext _db = new _dbDataContext();
        var val = _db.tblPhuongs.Single(r => r.ID == id);
        try { _db.tblPhuongs.DeleteOnSubmit(val); _db.SubmitChanges(); return true; }
        catch { return false; }
    }
    #endregion
    #region "Quản lý đoàn cơ sở"
    public static bool InsertDoanCoSo(string madonvi,string tendonvi,string diachi,string dienthoaiban,string dienthoaididong,string fax,string email,string website,string lanhdao,string nghiquyet,string ngaythanhlap,string tructhuoc,int doankhoi,int loaidonvi) //80:cơ sở đoàn, 81:đoàn cơ sở, 82: chi đoàn cơ sở, 83: chi đoàn
    {
        _dbDataContext _db = new _dbDataContext();
        tblCoSoDoan _tblcosodoan = new tblCoSoDoan();
        string st = "";
        if (tructhuoc.Substring(0, 3) == "TDB")//tinh doan
        {
            st = "CSD";
            _tblcosodoan.LoaiDonVi = 80;
            _tblcosodoan.MaDonVi = st +"-"+ madonvi;
        }
        else if (tructhuoc.Substring(0, 3) == "CSD")//co so doan
        {
            {
                if (loaidonvi == 81)
                    st = "DCS";//doan co so
                else
                    st = "CDC";//chi doan co so
                _tblcosodoan.LoaiDonVi = loaidonvi;
            }
            _tblcosodoan.MaDonVi = st + "-"+ tructhuoc.Substring(4) + madonvi;
        }
        else if (tructhuoc.Substring(0, 3) == "DCS" || tructhuoc.Substring(0, 3) == "CDC")
        {
            st = "CDD";//chi doan
            _tblcosodoan.LoaiDonVi = 83;
            _tblcosodoan.MaDonVi = st + "-" + tructhuoc.Substring(4) + madonvi;
        }
        _tblcosodoan.TenDonVi = tendonvi;
        _tblcosodoan.DiaChi = diachi;
        _tblcosodoan.DienThoaiBan = dienthoaiban;
        _tblcosodoan.DienThaoiDiDong = dienthoaididong;
        _tblcosodoan.Fax = fax;
        _tblcosodoan.Email = email;
        _tblcosodoan.Website = website;
        _tblcosodoan.LanhDao = lanhdao;
        _tblcosodoan.NghiQuyet = nghiquyet;
        _tblcosodoan.GiaiThe = false;
        try
        {
            int year = int.Parse(ngaythanhlap.Split('/')[2]);
            int month = int.Parse(ngaythanhlap.Split('/')[1]);
            int day = int.Parse(ngaythanhlap.Split('/')[0]);
            DateTime ngay = new DateTime(year, month, day);
            _tblcosodoan.NgayThanhLap = ngay;
        }
        catch { }
        _tblcosodoan.TrucThuoc = tructhuoc;
        _tblcosodoan.DoanKhoiID = doankhoi;
        try
        {
            _db.tblCoSoDoans.InsertOnSubmit(_tblcosodoan);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateDoanCoSo(string madonvi, string tendonvi, string diachi, string dienthoaiban, string dienthoaididong, string fax, string email, string website, string lanhdao, string nghiquyet, string ngaythanhlap, string tructhuoc,int doankhoi, int loaidonvi)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tblcosodoan = _db.tblCoSoDoans.Single(r=>r.MaDonVi.Trim().ToUpper()==madonvi.ToUpper());
        _tblcosodoan.TenDonVi = tendonvi;
        _tblcosodoan.DiaChi = diachi;
        _tblcosodoan.DienThoaiBan = dienthoaiban;
        _tblcosodoan.DienThaoiDiDong = dienthoaididong;
        _tblcosodoan.Fax = fax;
        _tblcosodoan.Email = email;
        _tblcosodoan.Website = website;
        _tblcosodoan.LanhDao = lanhdao;
        _tblcosodoan.NghiQuyet = nghiquyet;
        _tblcosodoan.DoanKhoiID = doankhoi;
        if (tructhuoc.Substring(0, 3) == "CSD")
        {
            _tblcosodoan.LoaiDonVi = loaidonvi;
        }
        try
        {
            int year = int.Parse(ngaythanhlap.Split('/')[2]);
            int month = int.Parse(ngaythanhlap.Split('/')[1]);
            int day = int.Parse(ngaythanhlap.Split('/')[0]);
            DateTime ngay = new DateTime(year, month, day);
            _tblcosodoan.NgayThanhLap = ngay;
        }
        catch { }
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteDoanCoSo(string madonvi)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tblcosodoan = _db.tblCoSoDoans.Single(r => r.MaDonVi.Trim().ToUpper() == madonvi.ToUpper());
        try
        {
            _db.tblCoSoDoans.DeleteOnSubmit(_tblcosodoan);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    #endregion
    #region "Quản lý đánh giá cơ sở đoàn"
    public static bool InsertDanhGia(int namdanhgia,string quyetdinh,int mucdo,string noidung,string madonvi)
    {
        _dbDataContext _db = new _dbDataContext();
        tblDanhGiaCSD _tbldanhgia = new tblDanhGiaCSD();
        _tbldanhgia.NamDanhGia = namdanhgia;
        _tbldanhgia.QuyetDinh = quyetdinh;
        _tbldanhgia.MucDo = mucdo;
        _tbldanhgia.NoiDung = noidung;
        _tbldanhgia.MaDonVi = madonvi;
        try
        {
            _db.tblDanhGiaCSDs.InsertOnSubmit(_tbldanhgia);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateDanhGia(int id,int namdanhgia, string quyetdinh, int mucdo, string noidung)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tbldanhgia = _db.tblDanhGiaCSDs.Single(r=>r.ID==id);
        _tbldanhgia.NamDanhGia = namdanhgia;
        _tbldanhgia.QuyetDinh = quyetdinh;
        _tbldanhgia.MucDo = mucdo;
        _tbldanhgia.NoiDung = noidung;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteDanhGia(int id)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tbldanhgia = _db.tblDanhGiaCSDs.Single(r => r.ID == id);
        try
        {
            _db.tblDanhGiaCSDs.DeleteOnSubmit(_tbldanhgia);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    #endregion
    #region "Quản lý tài khoản"
    public static bool InsertAcount(string tendangnhap,string matkhau,string tenhienthi,string email,string donvi,bool phanquyen)
    {
        SecurityLib.PasswordHasherEncrypt _ps = new SecurityLib.PasswordHasherEncrypt();
        _dbDataContext _db = new _dbDataContext();
        tblAcount _tblacount = new tblAcount();
        _tblacount.TenDangNhap = tendangnhap;
        _tblacount.MaKhau = _ps.Hash_SHA1(_ps.EncryptPassword_MD5(matkhau));
        _tblacount.TenHienThi = tenhienthi;
        _tblacount.Email = email;
        _tblacount.DonVi = donvi;
        _tblacount.PhanQuyen = phanquyen;
        try
        {
            _db.tblAcounts.InsertOnSubmit(_tblacount);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateAcount(int id,string tendangnhap, string matkhau, string tenhienthi, string email, string donvi, bool phanquyen,bool doimatkhau)
    {
        SecurityLib.PasswordHasherEncrypt _ps = new SecurityLib.PasswordHasherEncrypt();
        _dbDataContext _db = new _dbDataContext();
        var _tblacount = _db.tblAcounts.Single(r=>r.ID==id);
        _tblacount.TenDangNhap = tendangnhap;
        if (doimatkhau == true)
        {
            _tblacount.MaKhau = _ps.Hash_SHA1(_ps.EncryptPassword_MD5(matkhau));
        }
        _tblacount.TenHienThi = tenhienthi;
        _tblacount.Email = email;
        _tblacount.DonVi = donvi;
        _tblacount.PhanQuyen = phanquyen;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteAcount(string tendangnhap)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tblacount = _db.tblAcounts.Single(r => r.TenDangNhap.Trim().ToUpper() == tendangnhap.ToUpper());
        try
        {
            _db.tblAcounts.DeleteOnSubmit(_tblacount);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool KiemTraTrungTaiKhoan(string tendangnhap) //true la da co tai khoan do roi
    {
        _dbDataContext _db = new _dbDataContext();
        var _tblacount = _db.tblAcounts.Where(r => r.TenDangNhap.Trim().ToUpper()== tendangnhap.ToUpper());
        return (_tblacount.Count()!=0);
    }
    public static void InsertRoleWithUser(int userid)
    {
        _dbDataContext _db = new _dbDataContext();
        tblUserInRole _tbl;
        var listroles = _db.tblRoles.ToList();
        foreach (var item in listroles.Where(r=>r.ID!=listroles.Count)) //muc dich cua dieu kien o day la loai role cuoi ra, vi role cuoi luon phai bang true de vao duoc trang default
        {
            _tbl = new tblUserInRole();
            _tbl.UserID = userid;
            _tbl.RolesID = item.ID;
            _tbl.PhanQuyen = false;
            _db.tblUserInRoles.InsertOnSubmit(_tbl);
        }
        _tbl = new tblUserInRole(); //chen role cuoi vao userinrole va phan quyen la true
        _tbl.UserID = userid;
        _tbl.RolesID = listroles.Count;
        _tbl.PhanQuyen = true;
        _db.tblUserInRoles.InsertOnSubmit(_tbl);
        _db.SubmitChanges();
    }
    #endregion
    #region "Quản lý đoàn viên"
    //Thêm mới đoàn viên
    public static bool InsertDoanVien(string madoanvien,string imgurl,string hoten,bool phai,string ngaysinh,string noisinh,string cmnd,string ngaycapcmnd
            ,string noicapcmnd,string nguyenquan,string thuongtru,string choohiennay,string dienthoai,string email,int dantoc,int tongiao
            ,int honnhan,string xuatthan,string nghenghiep,string motacongviec,string trinhdovanhoa,int trinhdochuyenmon,string bangcap,int ngoaingu,int tinhoc
            ,string suckhoe,string ngayvaodoan,bool phattrienmoi,string ngaychuyenden,string donvicu,string donvitructhuoc,int trangthai,int diabandancu,bool dangvien,string chucvunoicongtac,int chucvudoanid)//trang thai 1 la doan vien, du bi la so 2
    {
        _dbDataContext _db = new _dbDataContext();
        tblDoanVien _tbldonvien = new tblDoanVien();
        //try
        //{
        //    _tbldonvien.MaDonVien = donvitructhuoc.Trim().Substring(4) + madoanvien;
        //}
        //catch { _tbldonvien.MaDonVien = madoanvien; }
        _tbldonvien.MaDonVien = madoanvien;
        if (imgurl != "")
        {
            _tbldonvien.ImgDoanVien = imgurl;
        }
        _tbldonvien.HoTen = hoten;
        _tbldonvien.Phai = phai;
        if (ngaysinh != "")
        {
            _tbldonvien.NgaySinh = ConvertToDate(ngaysinh);
        }
        _tbldonvien.NoiSinh = noisinh;
        _tbldonvien.CMND = cmnd;
        
        if (ngaycapcmnd != "")
        {
            _tbldonvien.NgayCapCMND = ConvertToDate(ngaycapcmnd);
        }
        _tbldonvien.NoiCap = noicapcmnd;
        _tbldonvien.NguyenQuan = nguyenquan;
        _tbldonvien.HoKhauThuongTru = thuongtru;
        _tbldonvien.ChoOHienNay = choohiennay;
        _tbldonvien.DienThoai = dienthoai;
        _tbldonvien.Email = email;
        if (dantoc != -1)
        {
            _tbldonvien.DanToc = dantoc;
        }
        if (tongiao != -1)
        {
            _tbldonvien.TonGiao = tongiao;
        }
        if (honnhan != -1)
        {
            _tbldonvien.HonNhan = honnhan;
        }
        _tbldonvien.XuatThan = xuatthan;
        _tbldonvien.NgheNghiep = nghenghiep;
        _tbldonvien.MoTaCongViec = motacongviec;
        _tbldonvien.TrinhDoVanHoa = trinhdovanhoa;
        if (trinhdochuyenmon != -1)
        {
            _tbldonvien.TrinhDoChuyenMonID = trinhdochuyenmon;
        }
        _tbldonvien.BangCap = bangcap;
        if (ngoaingu != -1)
        {
            _tbldonvien.NgoaiNgu = ngoaingu;
        }
        if (tinhoc != -1)
        {
            _tbldonvien.TinHoc = tinhoc;
        }
        _tbldonvien.SucKhoe = suckhoe;
        if (ngayvaodoan != "")
        {
            _tbldonvien.NgayVaoDoan = ConvertToDate(ngayvaodoan);
        }
        _tbldonvien.PhatTrienMoi = phattrienmoi;
        if (phattrienmoi == false)
        {
            if (ngaychuyenden != "")
            {
                _tbldonvien.NgayChuyenDen = ConvertToDate(ngaychuyenden);
            }
            _tbldonvien.DonViCu = donvicu;
        }
        _tbldonvien.TrucThuoc = donvitructhuoc;
        _tbldonvien.TrangThai = trangthai;
        _tbldonvien.TrangThaiXoa = false;
        if (diabandancu != -1)
        {
            _tbldonvien.DiaBanDanCuID = diabandancu;
        }
        _tbldonvien.DangVien = dangvien;
        _tbldonvien.DoanVienSinhHoatNoiCuTru = true;
        _tbldonvien.ChucVuNoiCongTac = chucvunoicongtac;
        _tbldonvien.ChucVuDoanID = chucvudoanid;
        try
        {
            _db.tblDoanViens.InsertOnSubmit(_tbldonvien);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    //cập nhật đoàn viên
    public static bool UpdateDoanVien(string madoanvien, string imgurl, string hoten, bool phai, string ngaysinh, string noisinh, string cmnd, string ngaycapcmnd
            , string noicapcmnd, string nguyenquan, string thuongtru, string choohiennay, string dienthoai, string email, int dantoc, int tongiao
            , int honnhan, string xuatthan, string nghenghiep, string motacongviec, string trinhdovanhoa, int trinhdochuyenmon, string bangcap, int ngoaingu, int tinhoc
            , string suckhoe, string ngayvaodoan, bool phattrienmoi, string ngaychuyenden, string donvicu, string donvitructhuoc, int diabandancu, bool dangvien)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tbldonvien = _db.tblDoanViens.Single(r=>r.MaDonVien.Trim()==madoanvien);
        if (imgurl != "")
        {
            _tbldonvien.ImgDoanVien = imgurl;
        }
        _tbldonvien.HoTen = hoten;
        _tbldonvien.Phai = phai;
        if (ngaysinh != "")
        {
            _tbldonvien.NgaySinh = ConvertToDate(ngaysinh);
        }
        _tbldonvien.NoiSinh = noisinh;
        _tbldonvien.CMND = cmnd;

        if (ngaycapcmnd != "")
        {
            _tbldonvien.NgayCapCMND = ConvertToDate(ngaycapcmnd);
        }
        _tbldonvien.NoiCap = noicapcmnd;
        _tbldonvien.NguyenQuan = nguyenquan;
        _tbldonvien.HoKhauThuongTru = thuongtru;
        _tbldonvien.ChoOHienNay = choohiennay;
        _tbldonvien.DienThoai = dienthoai;
        _tbldonvien.Email = email;
        if (dantoc != -1)
        {
            _tbldonvien.DanToc = dantoc;
        }
        if (tongiao != -1)
        {
            _tbldonvien.TonGiao = tongiao;
        }
        if (honnhan != -1)
        {
            _tbldonvien.HonNhan = honnhan;
        }
        _tbldonvien.XuatThan = xuatthan;
        _tbldonvien.NgheNghiep = nghenghiep;
        _tbldonvien.MoTaCongViec = motacongviec;
        _tbldonvien.TrinhDoVanHoa = trinhdovanhoa;
        if (trinhdochuyenmon != -1)
        {
            _tbldonvien.TrinhDoChuyenMonID = trinhdochuyenmon;
        }
        _tbldonvien.BangCap = bangcap;
        if (ngoaingu != -1)
        {
            _tbldonvien.NgoaiNgu = ngoaingu;
        }
        if (tinhoc != -1)
        {
            _tbldonvien.TinHoc = tinhoc;
        }
        _tbldonvien.SucKhoe = suckhoe;
        if (ngayvaodoan != "")
        {
            _tbldonvien.NgayVaoDoan = ConvertToDate(ngayvaodoan);
        }
        _tbldonvien.PhatTrienMoi = phattrienmoi;
        if (phattrienmoi == false)
        {
            if (ngaychuyenden != "")
            {
                _tbldonvien.NgayChuyenDen = ConvertToDate(ngaychuyenden);
            }
            _tbldonvien.DonViCu = donvicu;
        }
        _tbldonvien.TrucThuoc = donvitructhuoc;
        _tbldonvien.TrangThaiXoa = false;
        if (diabandancu != -1)
        {
            _tbldonvien.DiaBanDanCuID = diabandancu;
        }
        _tbldonvien.DangVien = dangvien;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    
    //cap nhat trang thai doan vien
    public static bool CapNhatTrangThaiDoanVien(string madoanvien,int trangthai)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblDoanViens.Single(r => r.MaDonVien.Trim() == madoanvien);
        _table.TrangThai = trangthai;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    //xoa thong tin truong thanh doan
    public static bool Xoa_tt_truongthanhdoan(string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        try
        {
            var _table = _db.tblTruongThanhKhaiTrus.Single(r => r.MaDoanVien.Trim() == madoanvien);
            _db.tblTruongThanhKhaiTrus.DeleteOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch
        {
            return false;
        }

    }
    //xoa doan vien tam thoi
    public static bool Xoa_PhucHoi_DoanVien(string madoanvien,bool trangthai)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblDoanViens.Single(r => r.MaDonVien.Trim() == madoanvien);
        _table.TrangThaiXoa = trangthai;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    //xoa doan vien tam thoi
    public static bool XoaDoanVienVinhVien(string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblDoanViens.Single(r => r.MaDonVien.Trim() == madoanvien);
        try
        {
            _db.tblDoanViens.DeleteOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    //insert khai tru/ truong thanh doan vien
    public static bool InsertKhaiTru_TruongThanh(string nghiquyet, string lydo, string ngay, string madoanvien, bool khaitru_truongthanh) //false la khai tru, true la truong thanh
    {
        _dbDataContext _db = new _dbDataContext();
        tblTruongThanhKhaiTru _table = new tblTruongThanhKhaiTru();
        _table.NghiQuyet = nghiquyet;
        _table.LyDo = lydo;
        _table.Ngay = ConvertToDate(ngay);
        _table.MaDoanVien = madoanvien;
        _table.TrungThanh = khaitru_truongthanh;
        try
        {
            _db.tblTruongThanhKhaiTrus.InsertOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }

    public static bool InsertHistoryDoanVien(string madoanvien, string nguoithuchien, string ngaythuchien, string noidungthuchien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblLichSuDoanVien _tb = new tblLichSuDoanVien();
        _tb.MaDoanVien = madoanvien;
        _tb.NoiDung = "<b>" + noidungthuchien + "</b><br/>" + "<span style=padding-left:25px;>Người thực hiện : " + nguoithuchien + "</span><br/><span style=padding-left:25px;>" + "Ngày thực hiện : " + ngaythuchien+"</span>";
        try
        {
            _db.tblLichSuDoanViens.InsertOnSubmit(_tb);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool QuanHeXaHoi(string madoanvien,int lyluanchinhtriid,int quanlynhanuocid,int thuongbinhid,int giadinhchinhsachid,string sotruong,
        string ngayvaodangdubi,string ngayvaodangchinhthuc,string ngaythamgiatcctxh,string noidungthamgiatcctxh,string ngaynhapngu,string ngayxuatngu,
        string quanham,int danhhieuid,string ngaynhandanhhieu,string khenthuong,string ngaynhankhenthuong,string kyluat,string ngaybikyluat)
    {
        _dbDataContext _db = new _dbDataContext();
        try
        {
            var _table = _db.tblQuanHeXaHois.Single(r => r.MaDoanVien.Trim() == madoanvien);
            if (lyluanchinhtriid != -1)
            {
                _table.LyLuanChinhTriID = lyluanchinhtriid;
            }
            if (quanlynhanuocid != -1)
            {
                _table.QuanLyNhaNuocID = quanlynhanuocid;
            }
            if (thuongbinhid != -1)
            {
                _table.ThuongBinhID = thuongbinhid;
            }
            if (giadinhchinhsachid != -1)
            {
                _table.GiaDinhChinhSachID = giadinhchinhsachid;
            }
            _table.SoTruong = sotruong;
            if (ngayvaodangdubi != "")
            {
                _table.NgayVaoDangDuBi = ConvertToDate(ngayvaodangdubi);
            }
            if (ngayvaodangchinhthuc != "")
            {
                _table.NgayVaoDangChinhThuc = ConvertToDate(ngayvaodangchinhthuc);
            }
            if (ngaythamgiatcctxh != "")
            {
                _table.NgayThamGiaTCCTXH = ConvertToDate(ngaythamgiatcctxh);
            }
            _table.CongViecTrongTCCTXH = noidungthamgiatcctxh;
            if (ngaynhapngu != "")
            {
                _table.NgayNhapNgu = ConvertToDate(ngaynhapngu);
            }
            if (ngayxuatngu != "")
            {
                _table.NgayXuatNgu = ConvertToDate(ngayxuatngu);
            }
            _table.QuanHam = quanham;
            if (danhhieuid != -1)
            {
                _table.DanhHieuID = danhhieuid;
            }
            if (ngaynhandanhhieu != "")
            {
                _table.NgayNhanDanhHieu = ConvertToDate(ngaynhandanhhieu);
            }
            _table.KhenThuong = khenthuong;
            if (ngaynhankhenthuong != "")
            {
                _table.NgayNhanKhenThuong = ConvertToDate(ngaynhankhenthuong);
            }
            _table.KyLuat = kyluat;
            if (ngaybikyluat != "")
            {
                _table.NgayBiKyLuat = ConvertToDate(ngaybikyluat);
            }
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        catch //truong hop chua co  thi them moi
        {
            tblQuanHeXaHoi _table = new tblQuanHeXaHoi();
            _table.MaDoanVien = madoanvien;
            if (lyluanchinhtriid != -1)
            {
                _table.LyLuanChinhTriID = lyluanchinhtriid;
            }
            if (quanlynhanuocid != -1)
            {
                _table.QuanLyNhaNuocID = quanlynhanuocid;
            }
            if (thuongbinhid != -1)
            {
                _table.ThuongBinhID = thuongbinhid;
            }
            if (giadinhchinhsachid != -1)
            {
                _table.GiaDinhChinhSachID = giadinhchinhsachid;
            }
            _table.SoTruong = sotruong;
            if (ngayvaodangdubi != "")
            {
                _table.NgayVaoDangDuBi = ConvertToDate(ngayvaodangdubi);
            }
            if (ngayvaodangchinhthuc != "")
            {
                _table.NgayVaoDangChinhThuc = ConvertToDate(ngayvaodangchinhthuc);
            }
            if (ngaythamgiatcctxh != "")
            {
                _table.NgayThamGiaTCCTXH = ConvertToDate(ngaythamgiatcctxh);
            }
            _table.CongViecTrongTCCTXH = noidungthamgiatcctxh;
            if (ngaynhapngu != "")
            {
                _table.NgayNhapNgu = ConvertToDate(ngaynhapngu);
            }
            if (ngayxuatngu != "")
            {
                _table.NgayXuatNgu = ConvertToDate(ngayxuatngu);
            }
            _table.QuanHam = quanham;
            if (danhhieuid != -1)
            {
                _table.DanhHieuID = danhhieuid;
            }
            if (ngaynhandanhhieu != "")
            {
                _table.NgayNhanDanhHieu = ConvertToDate(ngaynhandanhhieu);
            }
            _table.KhenThuong = khenthuong;
            if (ngaynhankhenthuong != "")
            {
                _table.NgayNhanKhenThuong = ConvertToDate(ngaynhankhenthuong);
            }
            _table.KyLuat = kyluat;
            if (ngaybikyluat != "")
            {
                _table.NgayBiKyLuat = ConvertToDate(ngaybikyluat);
            }
            try
            {
                _db.tblQuanHeXaHois.InsertOnSubmit(_table);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        
    }
    public static bool InsertQuanHeGiaDinh(string hoten,string ngaysinh,int quanheid,string thuongtru,string diachi,int thutu,string gioithieu,bool loai,string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblQuanHeGiaDinh _table = new tblQuanHeGiaDinh();
        _table.HoVaTen = hoten;
        _table.NgaySinh = ConvertToDate(ngaysinh);
        _table.QuanHeID = quanheid;
        _table.HoKhauThuongTru = thuongtru;
        _table.DiaChi = diachi;
        _table.ThuTu = thutu;
        _table.GioiThieu = gioithieu;
        _table.Loai = loai;
        _table.MaDoanVien = madoanvien;
        try
        {
            _db.tblQuanHeGiaDinhs.InsertOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }

    public static bool UpdateQuanHeGiaDinh(int idquanhegiadinh,string hoten, string ngaysinh, int quanheid, string thuongtru, string diachi, int thutu, string gioithieu)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblQuanHeGiaDinhs.Single(r=>r.ID==idquanhegiadinh);
        _table.HoVaTen = hoten;
        _table.NgaySinh = ConvertToDate(ngaysinh);
        _table.QuanHeID = quanheid;
        _table.HoKhauThuongTru = thuongtru;
        _table.DiaChi = diachi;
        _table.ThuTu = thutu;
        _table.GioiThieu = gioithieu;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }

    public static bool DeleteQuanHeGiaDinh(int idquanhegiadinh)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblQuanHeGiaDinhs.Single(r => r.ID == idquanhegiadinh);
        try
        {
            _db.tblQuanHeGiaDinhs.DeleteOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool InsertLyLichLamViec(string tencongviec, string tencongty, string tungay, string denngay, string chucvu, int thutu, string mota, string lydothoiviec, string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblLyLichLamViec _table = new tblLyLichLamViec();
        _table.TenCongViec = tencongty;
        _table.CongTy = tencongty;
        if (tungay != "")
        {
            _table.TuNgay = ConvertToDate(tungay);
        }
        if (denngay != "")
        {
            _table.DenNgay = ConvertToDate(denngay);
        }
        _table.ChucVu = chucvu;
        _table.ThuTu = thutu;
        _table.MoTaCongViec = mota;
        _table.LyDoThoiViec = lydothoiviec;
        _table.MaDoanVien = madoanvien;
        try
        {
            _db.tblLyLichLamViecs.InsertOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateLyLichLamViec(int id,string tencongviec, string tencongty, string tungay, string denngay, string chucvu, int thutu, string mota, string lydothoiviec)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblLyLichLamViecs.Single(r=>r.ID==id);
        _table.TenCongViec = tencongviec;
        _table.CongTy = tencongty;
        if (tungay != "")
        {
            _table.TuNgay = ConvertToDate(tungay);
        }
        if (denngay != "")
        {
            _table.DenNgay = ConvertToDate(denngay);
        }
        _table.ChucVu = chucvu;
        _table.ThuTu = thutu;
        _table.MoTaCongViec = mota;
        _table.LyDoThoiViec = lydothoiviec;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteLyLichCongViec(int id)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblLyLichLamViecs.Single(r => r.ID == id);
        try
        {
            _db.tblLyLichLamViecs.DeleteOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool InsertQuaTrinhHocTap(string tentruong,int loaitruong,string nganhhoc,string tungay,string denngay,int bangcap,int tinhtrang,string ghichu,int thutu,string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblQuaTrinhHocTap _table = new tblQuaTrinhHocTap();
        _table.TenTruong = tentruong;
        if (loaitruong != -1)
        {
            _table.LoaiTruongID = loaitruong;
        }
        _table.NganhHoc = nganhhoc;
        if (tungay != "")
        {
            _table.TuNgay = ConvertToDate(tungay);
        }
        if (denngay != "")
        {
            _table.DenNgay = ConvertToDate(denngay);
        }
        if (bangcap != -1)
        {
            _table.BangCapID = bangcap;
        }
        if (tinhtrang != -1)
        {
            _table.TinhTrangID = tinhtrang;
        }
        _table.GhiChu = ghichu;
        _table.ThuTu = thutu;
        _table.MaDoanVien = madoanvien;
        try
        {
            _db.tblQuaTrinhHocTaps.InsertOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateQuaTrinhHocTap(int id,string tentruong, int loaitruong, string nganhhoc, string tungay, string denngay, int bangcap, int tinhtrang, string ghichu, int thutu)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblQuaTrinhHocTaps.Single(r=>r.ID==id);
        _table.TenTruong = tentruong;
        if (loaitruong != -1)
        {
            _table.LoaiTruongID = loaitruong;
        }
        _table.NganhHoc = nganhhoc;
        if (tungay != "")
        {
            _table.TuNgay = ConvertToDate(tungay);
        }
        if (denngay != "")
        {
            _table.DenNgay = ConvertToDate(denngay);
        }
        if (bangcap != -1)
        {
            _table.BangCapID = bangcap;
        }
        if (tinhtrang != -1)
        {
            _table.TinhTrangID = tinhtrang;
        }
        _table.GhiChu = ghichu;
        _table.ThuTu = thutu;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteQuaTrinhHocTap(int id)
    {
        _dbDataContext _db = new _dbDataContext();
        var _table = _db.tblQuaTrinhHocTaps.Single(r => r.ID == id);
        try
        {
            _db.tblQuaTrinhHocTaps.DeleteOnSubmit(_table);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    #endregion
    #region "các thành phần khác"
    public static DateTime ConvertToDate(string datetime)
    {
        try
        {
            int year = int.Parse(datetime.Split('/')[2]);
            int month = int.Parse(datetime.Split('/')[1]);
            int day = int.Parse(datetime.Split('/')[0]);
            DateTime date = new DateTime(year, month, day);
            return date;
        }
        catch { return DateTime.Now; }
    }
    public static bool ConvertToDateBool(string datetime)
    {
        try
        {
            int year = int.Parse(datetime.Split('/')[2]);
            int month = int.Parse(datetime.Split('/')[1]);
            int day = int.Parse(datetime.Split('/')[0]);
            DateTime date = new DateTime(year, month, day);
            return true;
        }
        catch { return false; }
    }
    public static string ConvertToString(string datetime)
    {
        try
        {
            int year = int.Parse(datetime.Split('/')[2]);
            int month = int.Parse(datetime.Split('/')[1]);
            int day = int.Parse(datetime.Split('/')[0]);
            string date = year + "/" + month + "/" + day;
            return date;
        }
        catch { return null; }
    }
    public static void BindItemToDropDown(DropDownList drl,int loai)
    {
        drl.Items.Clear();
        ListItem item =new ListItem("---Chọn---","-1");
        drl.Items.Add(item);
        _dbDataContext _db=new _dbDataContext();
        var val=_db.tblDanhMucs.Where(r=>r.Loai==loai);
        foreach(var items in val)
        {
            item=new ListItem(items.TenDanhMuc.Trim(),items.ID.ToString());
            drl.Items.Add(item);
        }
    }
    public static int GetLastMaDonViOfDonViTrucThuong(string madonvi,int num)
    {
        _dbDataContext _db = new _dbDataContext();
        var val = from p in _db.tblCoSoDoans where p.TrucThuoc.Trim() == madonvi select new {id=p.MaDonVi.Trim().Substring(p.MaDonVi.Trim().Length-num)};
        if (val.Count() == 0)
            return 1;
        else
        {
            List<int> max = new List<int>();
            foreach (var item in val)
            {
                max.Add(int.Parse(item.id));
            }
            return (max.Max()+1);
        }
    }
    public static int GetLastMaDoanVienCuaDonVi(string madonvi)
    {
        _dbDataContext _db = new _dbDataContext();
        var val = from p in _db.tblDoanViens where p.TrucThuoc.Trim() == madonvi select new { id = p.MaDonVien.Substring(p.MaDonVien.Length - 8) };
        if (val.Count() == 0)
        {
            return 1;
        }
        else
        {
            List<int> max = new List<int>();
            foreach (var item in val)
            {
                max.Add(int.Parse(item.id));
            }
            return max.Max() + 1;
        }
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="loai">1: thẻ; 2: sổ</param>
    /// <returns></returns>
    public static string GetSoThe_SoSo(int loai,string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        string st = "";
        try
        {
            st = _db.tblTheSoDoans.Single(r => r.MaDoanVien == madoanvien && r.Loai == loai).SoDuocCap;
        }
        catch { }
        return st;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="madoanvien"></param>
    /// <param name="loai">1: the; 2: so</param>
    /// <returns></returns>
    public static bool KiemTraSo_The_DaCap(string madoanvien, int loai)
    {
        _dbDataContext _db = new _dbDataContext();
        return _db.tblTheSoDoans.Where(r => r.MaDoanVien.Trim() == madoanvien && r.Loai == loai).Count() != 0;
    }
    //đoàn viên danh dự
    public static bool InsertDoanVienDanhDu(string nghiquyet, string lydo, string ngayky, string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        tblDoanVienDanhDu _tb = new tblDoanVienDanhDu();
        _tb.NghiQuyet = nghiquyet;
        _tb.LyDo = lydo;
        _tb.Ngay = ConvertToDate(ngayky);
        _tb.MaDoanVien = madoanvien;
        try
        {
            _db.tblDoanVienDanhDus.InsertOnSubmit(_tb);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteDoanVienDanhDu(string madoanvien)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tb = _db.tblDoanVienDanhDus.Single(r => r.MaDoanVien.Trim() == madoanvien);
        try
        {
            _db.tblDoanVienDanhDus.DeleteOnSubmit(_tb);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    
    #endregion
    #region "quản lý phần tin tức"
    public static int GetIDAcountWithUserName(string username)
    {
        _dbDataContext _db = new _dbDataContext();
        return _db.tblAcounts.Single(r => r.TenDangNhap.Trim() == username).ID;
    }
    public static string GetName_DONVICS(string ID)
    {
        _dbDataContext _db = new _dbDataContext();
        return _db.tblCoSoDoans.Single(r => r.MaDonVi.Trim() == ID).TenDonVi;
    }

    public static bool InsertNews(int loai,string title,string content,int nguoidang,string donvinhan)
    {
        _dbDataContext _db = new _dbDataContext();
        tblNew _tblnew = new tblNew();
        _tblnew.Loai = loai;
        _tblnew.TieuDe = title;
        _tblnew.NoiDung = content;
        _tblnew.NguoiDang = nguoidang;
        _tblnew.NgayDang = DateTime.Now;
        _tblnew.NgayDangID = _db.Tinhsongay(DateTime.Now);
        _tblnew.DonViNhan = donvinhan;
        _tblnew.DaDoc = "";
        try
        {
            _db.tblNews.InsertOnSubmit(_tblnew);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool UpdateNews(int id, int loai, string title, string content)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tblnew = _db.tblNews.Single(r=>r.ID==id);
        _tblnew.TieuDe = title;
        _tblnew.Loai = loai;
        _tblnew.NoiDung = content;
        try
        {
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    public static bool DeleteNews(int id)
    {
        _dbDataContext _db = new _dbDataContext();
        var _tblnew = _db.tblNews.Single(r => r.ID == id);
        try
        {
            _db.tblNews.DeleteOnSubmit(_tblnew);
            _db.SubmitChanges();
            return true;
        }
        catch { return false; }
    }
    #endregion
    #region Đổi ngày tháng sang định dạng số
     //hàm chuyển đổi ngày sinh sang tuổi
    public static double tinhtuoi(DateTime ngaysinh)
    {
        DateTime now_dt = DateTime.Now;
        int numDaysDiff = now_dt.Subtract(ngaysinh).Days;
        double tuoi = Math.Round((double)(numDaysDiff / 365), MidpointRounding.ToEven);
        return tuoi;
    }
    //hàm chuyển đổi ngày sinh ra số ngày
    public static double songaysinh(DateTime ngaysinh)
    {
        DateTime now_dt = DateTime.Now;
        int numDaysDiff = now_dt.Subtract(ngaysinh).Days;
        return numDaysDiff;
    }
    //hàm chuyển đổi ngày sang số kiểu double
    public static double doingaysangso(DateTime ngaysinh)
    {
        DateTime now_dt = DateTime.Now;
        int numDaysDiff = now_dt.Subtract(ngaysinh).Days;
        return numDaysDiff;
    }
    public static int DoiNgaySangSoTinhTu1930(DateTime ngay)
    {
        DateTime now_dt = new DateTime(1930, 1, 1);
        int numDaysDiff = ngay.Subtract(now_dt).Days;
        return numDaysDiff;
    }
    #endregion
    #region truy vấn tìm kiếm đoàn viên tùy chọn
    public static DataTable Truyvan(string SQL_)
    {
        DataTable dt = new DataTable();
        string strConnection = ConfigurationManager.ConnectionStrings["DoanVienConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(strConnection);
        SqlDataAdapter da = new SqlDataAdapter(SQL_, conn);
        da.Fill(dt);
        return dt;
        conn.Close();
        
    }
    #endregion
}
